﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace KC.DataAccess.Global
{
	/// <summary>Global Data Access. All direct SQL methods should go through here.</summary>
	public static class Global
	{
		/// <summary>Execute a SQL Stored Procedure</summary>
		/// <param name="cs">ConnectionString</param>
		/// <param name="sproc">Stored Procedure name</param>
		/// <param name="parms">SQL Parameters</param>
		/// <returns>DataTable with result/cursor</returns>
		public static DataTable CallSql_Sproc(string cs, string sproc, SqlParameter[] parms)
		{
			DataTable dt = new DataTable();
			if (string.IsNullOrEmpty(cs)) throw new ArgumentNullException("cs");
			if (string.IsNullOrEmpty(sproc)) throw new ArgumentNullException("sproc");
			if (parms == null) parms = new SqlParameter[0];

			using (SqlConnection conn = new SqlConnection(cs))
			using (SqlCommand comm = new SqlCommand(sproc, conn))
			{
				comm.CommandType = CommandType.StoredProcedure;
				conn.Open();
				foreach (SqlParameter parm in parms)
					comm.Parameters.Add(parm);
				using (SqlDataAdapter da = new SqlDataAdapter(comm))
					da.Fill(dt);
				conn.Close();
			}
			return dt;
		}
		/// <summary>Calls SQL with a generic query</summary>
		/// <param name="cs">Connection String</param>
		/// <param name="query">SQL Query</param>
		/// <returns>DataTable containing query returned data</returns>
		public static DataTable CallSql_Query(string cs, string query)
		{
			if (string.IsNullOrEmpty(cs)) throw new ArgumentNullException("cs");
			if (string.IsNullOrEmpty(query)) throw new ArgumentNullException("query");

			DataTable dt = new DataTable();
			using (SqlConnection con = new SqlConnection(cs))
			using (SqlDataAdapter da = new SqlDataAdapter(query, con))
				da.Fill(dt);
			return dt;
		}
		/// <summary>Calls SQL with a stored proc</summary>
		/// <param name="cs">Connection String</param>
		/// <param name="sproc">Stored Procedure name</param>
		/// <returns>DataTable containing query returned data</returns>
		public static DataTable CallSql_Sproc(string cs, string sproc)
		{ return CallSql_Sproc(cs, sproc, new SqlParameter[0]); }
	}
}
